package com.shangyueshang.mapper;

import com.shangyueshang.model.CompanyRevenue;
import com.shangyueshang.model.ShopRevenue;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author: JokeyZheng
 * @email: zhengjingfeng@ruqimobility.com
 * @created: 2022/1/19 14:46
 * @version: v1.0.0
 */
@Component
public interface RevenueMapper {
	
	/**
	 * 从店铺维度查询
	 *
	 * @param params
	 * @return
	 */
	@Select("<script>" +
			"   SELECT " +
			"       ps.shop, " +
			"       ps.trade_month AS tradeMonth, " +
			"       ps.advertise_fee AS advertiseFee, " +
			"       ps.subscription_fee AS subscriptionFee, " +
			"       ps.adjustment_fee AS adjustmentFee, " +
			"       ps.deal_fee AS dealFee, " +
			"       ps.debt_fee AS debtFee, " +
			"       ps.coupon_redemption_fee AS couponRedemptionFee, " +
			"       ps.fba_inbound_placement_service_fee AS fbaInboundPlacementServiceFee, " +
			"       ps.inbound_defect_fee AS inboundDefectFee, " +
			"       ps.early_reviewer_fee AS earlyReviewerFee, " +
			"       ps.fba_inventory_fee AS fbaInventoryFee, " +
			"       ps.adjustment_compensate AS adjustmentCompensate, " +
			"       ps.awd_fee AS awdFee, " +
			"       ps.send_shipping_charge AS sendShippingCharge, " +
			"       gr.product_sales AS productSales, " +
			"       gr.sale_revenue AS saleRevenue, " +
			"       gr.sale_quantity AS saleQuantity, " +
			"       gr.fba_fee AS fbaFee, " +
			"       gr.selling_fee AS sellingFee, " +
			"       gr.purchase_fee AS purchaseFee, " +
			"       gr.headway_fee AS headwayFee, " +
			"       gr.pack_fee AS packFee, " +
			"       gr.return_fee AS returnFee, " +
			"       gr.return_quantity AS returnQuantity  " +
			"   FROM  " +
			"       platform_spend ps, (" +
			"   SELECT " +
			"       shop, " +
			"       DATE_FORMAT( trade_date, '%Y-%m' ) AS trade_month, " +
			"       SUM( product_sales ) AS product_sales, " +
			"       SUM( sale_revenue ) AS sale_revenue, " +
			"       SUM( sale_quantity ) AS sale_quantity, " +
			"       SUM( selling_fee ) AS selling_fee, " +
			"       SUM( fba_fee ) AS fba_fee, " +
			"       SUM( headway_fee ) AS headway_fee, " +
			"       SUM( purchase_fee ) AS purchase_fee, " +
			"       SUM( pack_fee ) AS pack_fee, " +
			"       SUM( return_fee ) AS return_fee, " +
			"       SUM( return_quantity ) AS return_quantity " +
			"   FROM  " +
			"       goods_revenue  " +
			"   GROUP BY shop, trade_month " +
			"   ) gr " +
			"   WHERE " +
			"       ps.shop = gr.shop " +
			"       AND gr.trade_month = ps.trade_month " +
			"       <if test='params.shop != null'> " +
			"		 AND ps.shop IN " +
			"			<foreach collection='params.shop' item='item' open='(' separator=',' close=')'>" +
			"				#{item} " +
			"			</foreach>" +
			"       </if> " +
			"       <if test='params.from_trade_month != null'> " +
			"           AND ps.trade_month <![CDATA[ >= ]]> #{params.from_trade_month} " +
			"       </if> " +
			"       <if test='params.to_trade_month != null'> " +
			"           AND ps.trade_month <![CDATA[ <= ]]> #{params.to_trade_month} " +
			"       </if> " +
			"   ORDER BY ps.trade_month DESC" +
			"</script>")
	List<ShopRevenue> queryShopRevenue(@Param("params") Map<String, Object> params);
	
	/**
	 * 从公司角度统计收入
	 *
	 * @param params
	 * @return
	 */
	@Select("<script>" +
			"   SELECT " +
			"       gr.*, " +
			"       fc.fixedCost, " +
			"		fc.actualHeadwayFee, " +
			"		ps.platformSpendFee " +
			"	FROM ( " +
			"     SELECT " +
			"		( " +
			"       SUM( art_fee ) " +
			"       + SUM( sample_fee ) " +
			"       + SUM( pack_material_fee ) " +
			"       + SUM( labor_fee ) " +
			"       + SUM( office_rent_fee ) " +
			"       + SUM( accounting_fee ) " +
			"       + SUM( headway_fee ) " +
			"       + SUM( other_fee ) " +
			"     ) AS fixedCost, " +
			"     SUM( headway_fee ) AS actualHeadwayFee," +
			"	  trade_month  " +
			"   FROM " +
			"       fixed_cost " +
			"	GROUP BY trade_month 	" +
			"	) fc, " +
			"   (   " +
			"       SELECT " +
			"           trade_month, " +
			"         ( " +
			"           SUM(advertise_fee)  " +
			"           + SUM(subscription_fee) " +
			"           + SUM(adjustment_fee) " +
			"           + SUM(deal_fee) " +
			"           + SUM(coupon_redemption_fee) " +
			"           + SUM(fba_inbound_placement_service_fee) " +
			"           + SUM(inbound_defect_fee) " +
			"           + SUM(early_reviewer_fee) " +
			"           + SUM(fba_inventory_fee) " +
			"           + SUM(debt_fee) " +
			"           + SUM(adjustment_compensate) " +
			"           + SUM(awd_fee) " +
		//	"           + SUM(send_shipping_charge) " +
			"           ) AS platformSpendFee " +
			"       FROM " +
			"           platform_spend " +
			"       GROUP BY trade_month " +
			"   ) ps, " +
			"   ( " +
			"       SELECT " +
			"           DATE_FORMAT( trade_date, '%Y-%m' ) AS tradeMonth, " +
			"           SUM( product_sales ) AS productSales, " +
			"           SUM( sale_revenue ) AS saleRevenue, " +
			"           SUM( headway_fee ) AS estimateHeadwayFee, " +
			"           SUM( purchase_fee ) AS purchaseFee, " +
			"           SUM( pack_fee ) AS packFee, " +
			"           SUM( return_fee ) AS returnFee " +
			"       FROM " +
			"           goods_revenue " +
			"       GROUP BY tradeMonth " +
			"   )   gr " +
			"   WHERE " +
			"       ps.trade_month = fc.trade_month  " +
			"       AND gr.tradeMonth = fc.trade_month " +
			"       <if test='params.from_trade_month != null'> " +
			"           AND fc.trade_month <![CDATA[ >= ]]> #{params.from_trade_month} " +
			"       </if> " +
			"       <if test='params.to_trade_month != null'> " +
			"           AND fc.trade_month <![CDATA[ <= ]]> #{params.to_trade_month} " +
			"       </if> " +
			"       ORDER BY fc.trade_month DESC " +
			"</script>")
	List<CompanyRevenue> queryCompanyRevenue(@Param("params") Map<String, Object> params);
}
